package com.studyweb.config;

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.context.SmartLifecycle;

import javax.sql.DataSource;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 数据库初始化器
 * 
 * <p>该组件负责在应用启动时检查并初始化数据库表结构，
 * 只有当表不存在时才会执行对应的建表脚本，避免数据丢失。</p>
 * 
 * @author StudyWeb Team
 * @since 1.0.0
 */
@Slf4j
@Component
public class DatabaseInitializer implements SmartLifecycle {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private boolean running = false;

    /**
     * 应用启动后自动执行数据库初始化检查
     */
    @Override
    public void start() {
        if (running) {
            return;
        }
        
        log.info("=== 开始数据库初始化检查 ===");
        
        try {
            // 检查并初始化各个业务表
            log.info("开始检查业务表初始化...");
            initializeTableIfNotExists("politics_question", "db/politics_question.sql");
            initializeTableIfNotExists("common_sense_question", "db/common_sense_question.sql");
            initializeTableIfNotExists("data_analysis_question", "db/data_analysis_question.sql");
            initializeTableIfNotExists("judgment_question", "db/judgment_question.sql");
            initializeTableIfNotExists("language_question", "db/language_question.sql");
            initializeTableIfNotExists("quantitative_question", "db/quantitative_question.sql");
            initializeTableIfNotExists("vocabulary", "db/vocabulary.sql");
            initializeTableIfNotExists("file_upload", "db/file_upload.sql");
            initializeTableIfNotExists("common_sense_accumulation", "db/common_sense_accumulation.sql");
            initializeTableIfNotExists("politics_accumulation", "db/politics_accumulation.sql");
            initializeTableIfNotExists("writing_material_accumulation", "db/writing_material_accumulation.sql");
            
            // 用户相关表（如果首次创建，需要初始化管理员用户）
            boolean userCreated = initializeTableIfNotExists("user", "db/user.sql");
            initializeTableIfNotExists("user_points", "db/user_points.sql");
            initializeTableIfNotExists("user_check_in", "db/user_check_in.sql");
            initializeTableIfNotExists("user_achievement", "db/user_achievement.sql");
            
            // 成就相关表（如果首次创建，需要初始化成就定义）
            boolean achievementCreated = initializeTableIfNotExists("achievement", "db/achievement.sql");
            
            initializeTableIfNotExists("wrong_question", "db/wrong_question.sql");
            initializeTableIfNotExists("shenlun_item", "db/shenlun_item.sql");
            initializeTableIfNotExists("note", "db/note.sql");
            // 添加试卷相关表
            initializeTableIfNotExists("paper", "db/paper.sql");
            initializeTableIfNotExists("paper_question", "db/paper_question.sql");
            initializeTableIfNotExists("paper_result", "db/paper_result.sql");
            // 添加试卷分值相关表（如果是首次创建，则标记需要初始化基础数据）
            boolean paperScoreCreated = initializeTableIfNotExists("paper_score", "db/paper_score.sql");
            boolean paperScoreDetailCreated = initializeTableIfNotExists("paper_score_detail", "db/paper_score_detail.sql");
            
            // 根据不同表的创建状态，精确初始化对应的基础数据
            if (userCreated) {
                log.info("检测到 user 表首次创建，开始初始化用户基础数据...");
                initializeUserBaseData();
            }
            
            if (achievementCreated) {
                log.info("检测到 achievement 表首次创建，开始初始化成就基础数据...");
                initializeAchievementBaseData();
            }
            
            if (paperScoreCreated || paperScoreDetailCreated) {
                log.info("检测到试卷分值表首次创建，开始初始化试卷分值基础数据...");
                initializePaperScoreBaseData();
            }
            
            if (!userCreated && !achievementCreated && !paperScoreCreated && !paperScoreDetailCreated) {
                log.info("所有表已存在，跳过基础数据初始化");
            }
            
            log.info("=== 数据库初始化检查完成 ===");
            running = true;
        } catch (Exception e) {
            log.error("数据库初始化检查失败", e);
        }
    }

    /**
     * 初始化用户模块基础数据
     */
    private void initializeUserBaseData() {
        try {
            log.info("正在初始化用户基础数据...");
            executeSqlScript("db/init_data_user.sql");
            log.info("用户基础数据初始化完成");
        } catch (Exception e) {
            log.error("用户基础数据初始化失败", e);
        }
    }

    /**
     * 初始化成就模块基础数据
     */
    private void initializeAchievementBaseData() {
        try {
            log.info("正在初始化成就基础数据...");
            executeSqlScript("db/init_data_achievement.sql");
            log.info("成就基础数据初始化完成");
        } catch (Exception e) {
            log.error("成就基础数据初始化失败", e);
        }
    }

    /**
     * 初始化试卷分值模块基础数据
     */
    private void initializePaperScoreBaseData() {
        try {
            log.info("正在初始化试卷分值基础数据...");
            executeSqlScript("db/init_data_paper_score.sql");
            log.info("试卷分值基础数据初始化完成");
        } catch (Exception e) {
            log.error("试卷分值基础数据初始化失败", e);
        }
    }

    /**
     * 检查表是否存在，如果不存在则执行初始化脚本
     * 
     * @param tableName 表名
     * @param scriptPath 脚本路径
     * @return 是否新建了表（true=新建，false=已存在）
     * @throws IOException IO异常
     * @throws SQLException SQL异常
     */
    private boolean initializeTableIfNotExists(String tableName, String scriptPath) throws IOException, SQLException {
        if (!doesTableExist(tableName)) {
            log.info("表 {} 不存在，正在执行初始化脚本: {}", tableName, scriptPath);
            executeSqlScript(scriptPath);
            log.info("表 {} 初始化完成", tableName);
            return true; // 返回true表示新建了表
        } else {
            log.debug("表 {} 已存在，跳过初始化", tableName);
            return false; // 返回false表示表已存在
        }
    }

    /**
     * 检查指定表是否存在
     * 
     * @param tableName 表名
     * @return 表是否存在
     * @throws SQLException SQL异常
     */
    private boolean doesTableExist(String tableName) throws SQLException {
        try (Connection connection = jdbcTemplate.getDataSource().getConnection()) {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet resultSet = metaData.getTables(connection.getCatalog(), null, tableName, new String[]{"TABLE"});
            return resultSet.next();
        }
    }

    /**
     * 执行SQL脚本文件
     * 
     * @param scriptPath 脚本路径
     * @throws IOException IO异常
     */
    private void executeSqlScript(String scriptPath) throws IOException {
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        Resource resource = resolver.getResource("classpath:" + scriptPath);
        
        if (!resource.exists()) {
            log.warn("SQL脚本文件不存在: {}", scriptPath);
            return;
        }
        
        // 读取脚本内容
        String sqlScript = new String(resource.getInputStream().readAllBytes(), StandardCharsets.UTF_8);
        log.debug("正在执行SQL脚本: {}, 脚本大小: {} 字节", scriptPath, sqlScript.length());
        
        // 使用更准确的方式分割SQL语句
        int statementCount = 0;
        StringBuilder currentStatement = new StringBuilder();
        String[] lines = sqlScript.split("\n");
        boolean inMultiLineComment = false;
        
        for (String line : lines) {
            String trimmedLine = line.trim();
            
            // 处理多行注释的开始和结束
            if (trimmedLine.startsWith("/*")) {
                inMultiLineComment = true;
            }
            if (inMultiLineComment && trimmedLine.contains("*/")) {
                inMultiLineComment = false;
                continue; // 跳过多行注释的最后一行
            }
            
            // 如果在多行注释中，跳过当前行
            if (inMultiLineComment) {
                continue;
            }
            
            // 跳过单行注释
            if (isComment(trimmedLine)) {
                continue;
            }
            
            // 添加行内容到当前语句
            currentStatement.append(line).append("\n");
            
            // 如果行以分号结尾，说明一个完整的语句结束了
            if (trimmedLine.endsWith(";")) {
                String statement = currentStatement.toString().trim();
                if (!statement.isEmpty() && !isOnlyWhitespaceOrComment(statement)) {
                    try {
                        statementCount++;
                        // 对于较长的SQL语句，显示前150个字符
                        String logSql = statement.replaceAll("\\s+", " ").trim();
                        log.debug("执行SQL语句 #{}: {}", statementCount, logSql.substring(0, Math.min(logSql.length(), 150)) + (logSql.length() > 150 ? "..." : ""));
                        jdbcTemplate.execute(statement);
                    } catch (Exception e) {
                        // 记录但不中断执行过程，某些语句可能因数据库差异而失败
                        String errorSql = statement.replaceAll("\\s+", " ").trim();
                        log.warn("执行SQL语句时出现警告（已跳过）: {}", errorSql.substring(0, Math.min(errorSql.length(), 150)) + (errorSql.length() > 150 ? "..." : ""), e);
                    }
                }
                // 重置当前语句
                currentStatement = new StringBuilder();
            }
        }
        
        // 处理最后一个可能没有分号结尾的语句（虽然不应该出现）
        String lastStatement = currentStatement.toString().trim();
        if (!lastStatement.isEmpty() && !isOnlyWhitespaceOrComment(lastStatement)) {
            try {
                statementCount++;
                String logSql = lastStatement.replaceAll("\\s+", " ").trim();
                log.debug("执行SQL语句 #{}: {}", statementCount, logSql.substring(0, Math.min(logSql.length(), 150)) + (logSql.length() > 150 ? "..." : ""));
                jdbcTemplate.execute(lastStatement);
            } catch (Exception e) {
                String errorSql = lastStatement.replaceAll("\\s+", " ").trim();
                log.warn("执行SQL语句时出现警告（已跳过）: {}", errorSql.substring(0, Math.min(errorSql.length(), 150)) + (errorSql.length() > 150 ? "..." : ""), e);
            }
        }
        
        log.debug("SQL脚本 {} 执行完成，共执行 {} 条语句", scriptPath, statementCount);
    }

    /**
     * 判断是否为注释行
     * 
     * @param sql SQL语句
     * @return 是否为注释
     */
    private boolean isComment(String sql) {
        return sql.startsWith("/*") || sql.startsWith("--") || sql.startsWith("#");
    }
    
    /**
     * 判断是否只包含空白字符或注释
     * 
     * @param sql SQL语句
     * @return 是否只包含空白字符或注释
     */
    private boolean isOnlyWhitespaceOrComment(String sql) {
        String[] lines = sql.split("\n");
        for (String line : lines) {
            String trimmedLine = line.trim();
            if (!trimmedLine.isEmpty() && !isComment(trimmedLine)) {
                return false;
            }
        }
        return true;
    }

    @Override
    public void stop() {
        running = false;
    }

    @Override
    public boolean isRunning() {
        return running;
    }

    @Override
    public boolean isAutoStartup() {
        return true;
    }

    @Override
    public void stop(Runnable callback) {
        running = false;
        callback.run();
    }

    @Override
    public int getPhase() {
        return 0;
    }
}